import datetime
import zipfile
from io import BytesIO

from flask import jsonify, request, Response
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from sqlalchemy import desc, text, union

from tzq.document_management_system import dms_bp
from tzq.document_management_system.dao.property_dao import generate_excel_file
from tzq.document_management_system.model.catalog_model import Catalog, CatalogModify, CatalogDelete
from tzq.document_management_system.model.function_model import Function, FunctionPermission, FunctionCatalog, \
    FunctionDimension
from tzq.document_management_system.model.log_model import Version
from tzq.document_management_system.model.lookup_model import LookupClassify, LookupItem, LookupDelete, LookupDisable
from tzq.document_management_system.model.message_model import Message
from tzq.document_management_system.model.property_model import Property
from tzq.document_management_system.model.role_model import Role, RoleFunction, RoleFunctionDelete
from tzq.extensions import db


@dms_bp.route('/api/getOneClickExportOnlineDate', methods=['GET'])
def get_one_click_export_online_date():
    # 前端页面下拉框展示使用
    try:
        # 查询 delete_flag = 'N' 的 去重后 根据 online_date 倒叙排序的所有 online_date 的值
        data_list = db.session.query(Function.online_date.label('online_date')).filter(
            Function.delete_flag == 'N').distinct()
        subquery_b = db.session.query(Version.online_date.label('online_date')).distinct()
        union_query = union(data_list, subquery_b)
        result = db.session.execute(union_query.order_by(desc(text('online_date')))).fetchall()

        # 提取结果（因为返回的是元组列表）, 提取元组中的第一个元素
        online_date_data = [{"online_date": item[0].strftime('%Y-%m-%d')} for item in result]
        return jsonify({"code": 0, "msg": "", "count": len(online_date_data), "data": online_date_data})
    except Exception as e:
        print("Database error:", e)
        return jsonify({"code": -1, "msg": str(e), "count": 0, "data": None})


@dms_bp.route('/api/oneClickExportToZip', methods=['GET', 'POST'])
def one_click_export_to_zip():
    # 一键导出 成 zip 文件
    # 入参
    principal = request.values.get("principal")
    online_date = request.values.get("online_date")
    created_by = request.values.get("created_by")

    # 定义Excel表头样式
    header_font = Font(name='Arial', bold=True, color="000000", size=12)
    header_fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    center_alignment = Alignment(horizontal="center", vertical="center")

    # 版本字符串
    if online_date:
        version_string = "(v." + online_date + ")-"
    else:
        version_string = "ALL-"
    # 日期字符串
    date_string = datetime.datetime.now().strftime("%Y%m%d%H%M%S")

    # 在内存中创建 ZIP 包
    zip_buffer = BytesIO()
    with (zipfile.ZipFile(zip_buffer, 'w', zipfile.ZIP_DEFLATED) as zip_file):
        # 国际化生成excel文件
        query = Message.query
        query = query.filter(Message.delete_flag == 'N')
        if principal: query = query.filter(Message.principal.ilike(f'%{principal}%'))
        if online_date: query = query.filter(Message.online_date == f'{online_date}')
        if created_by: query = query.filter(Message.created_by.ilike(f'%{created_by}%'))
        query = query.order_by(Message.online_date.desc(), Message.last_update_date.desc())
        query = query.paginate(page=1, per_page=1000000, error_out=False)
        if query.total > 0:
            # 如果有数据，则生成 excel 文件
            # 创建 Excel 工作簿和工作表
            wb = Workbook()
            ws = wb.active
            ws.title = "I18n"
            # 写入表头
            headers = [
                'Key',
                'IContent',
                'Language',
                'Remark',
                'Last Update Date',
                '责任人', '上线日期'
            ]
            ws.append(headers)
            # 为表头行（第1行）设置样式
            for col_num, header in enumerate(headers, 1):
                cell = ws.cell(row=1, column=col_num)
                cell.font = header_font
                cell.fill = header_fill
                cell.border = thin_border
                cell.alignment = center_alignment
            # 写入数据
            for item in query:
                ws.append([item.key,
                           item.value,
                           item.language,
                           '',
                           '',  # 最后更新时间可以不写，导入的时候就是当前时间了
                           item.principal,
                           item.online_date.strftime('%Y-%m-%d')
                           ])
            # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
            ws.freeze_panes = 'A2'
            # 启用自动筛选：从 A1 到最后一列最后一行
            last_col_letter = ws.cell(row=1, column=ws.max_column).column_letter
            last_row = ws.max_row
            ws.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
            # 调整列宽
            ws.column_dimensions['A'].width = 50  # Key
            ws.column_dimensions['B'].width = 50  # IContent
            ws.column_dimensions['C'].width = 15  # Language
            ws.column_dimensions['D'].width = 15  # Remark
            ws.column_dimensions['E'].width = 21  # Last Update Date
            ws.column_dimensions['F'].width = 15  # 责任人
            ws.column_dimensions['G'].width = 15  # 上线日期
            # 设置某几列单元格居中显示
            for col in ['C', 'G']:
                for row in ws.iter_rows(min_col=ws[col + '1'].column, max_col=ws[col + '1'].column):
                    for cell in row:
                        cell.alignment = Alignment(horizontal='center', vertical='center')

            # 将 workbook 保存到 BytesIO
            excel_buffer = BytesIO()
            wb.save(excel_buffer)
            excel_buffer.seek(0)
            filename = "jalor-I18n-" + version_string + date_string + ".xlsx"
            # 添加到 ZIP
            zip_file.writestr(filename, excel_buffer.read())

        # Lookup 生成excel文件
        # 1、导出 lookup 的数据
        query = LookupClassify.query
        query = query.filter(LookupClassify.delete_flag == 'N')
        if principal: query = query.filter(LookupClassify.principal.ilike(f'%{principal}%'))
        if online_date: query = query.filter(LookupClassify.online_date == f'{online_date}')
        if created_by: query = query.filter(LookupClassify.created_by.ilike(f'%{created_by}%'))
        query = query.order_by(LookupClassify.online_date.desc(), LookupClassify.last_update_date.desc())
        query = query.paginate(page=1, per_page=1000000, error_out=False)
        lookup_total = query.total
        wb = Workbook()
        ws = wb.active
        ws.title = "Lookup Classify"
        # 写入表头
        headers = [
            # 表头 - 业务字段
            'Code',
            'Parent Classify',
            'Name',
            'Status',
            'Lookup Type',
            'Desc',
            'Public Wirte',

            '责任人', '上线日期'
        ]
        ws.append(headers)
        # 为表头行（第1行）设置样式
        for col_num, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col_num)
            cell.font = header_font
            cell.fill = header_fill
            cell.border = thin_border
            cell.alignment = center_alignment
        # 写入数据
        for item in query:
            ws.append([
                # 数据 - 业务字段
                item.classify_code,
                '',
                item.classify_name,
                '1',
                'COMMON',
                item.classify_desc,
                '1',

                item.principal,
                item.online_date.strftime('%Y-%m-%d')
            ])
        # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
        ws.freeze_panes = 'A2'
        # 启用自动筛选：从 A1 到最后一列最后一行
        last_col_letter = ws.cell(row=1, column=ws.max_column).column_letter
        last_row = ws.max_row
        ws.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
        # 调整列宽
        ws.column_dimensions['A'].width = 37  # Code
        ws.column_dimensions['B'].width = 18  # Parent Classify
        ws.column_dimensions['C'].width = 37  # Name
        ws.column_dimensions['D'].width = 10  # Status
        ws.column_dimensions['E'].width = 18  # Lookup Type
        ws.column_dimensions['F'].width = 37  # Desc
        ws.column_dimensions['G'].width = 18  # Public Wirte
        ws.column_dimensions['H'].width = 13  # 责任人
        ws.column_dimensions['I'].width = 13  # 上线日期
        # 设置某几列单元格居中显示
        for col in ['D', 'E', 'G', 'I']:
            for row in ws.iter_rows(min_col=ws[col + '1'].column, max_col=ws[col + '1'].column):
                for cell in row:
                    cell.alignment = Alignment(horizontal='center', vertical='center')

        # 2、导出 lookup item 的数据
        query = LookupItem.query
        query = query.filter(LookupItem.delete_flag == 'N')
        if principal: query = query.filter(LookupItem.principal.ilike(f'%{principal}%'))
        if online_date: query = query.filter(LookupItem.online_date == f'{online_date}')
        if created_by: query = query.filter(LookupItem.created_by.ilike(f'%{created_by}%'))
        query = query.order_by(LookupItem.online_date.desc(), LookupItem.last_update_date.desc())
        query = query.paginate(page=1, per_page=1000000, error_out=False)
        lookup_item_total = query.total
        # 创建第二个工作表
        ws2 = wb.create_sheet("Lookup Item")
        # 写入表头
        headers = [
            'Classify Code',
            'Item Code',
            'Item Name',
            'Desc',
            'Parent Item',
            'Status',
            'Lookup Type',
            'Language',
            'Sort',
            'Attribute1', 'Attribute2', 'Attribute3', 'Attribute4', 'Attribute5', 'Attribute6',

            '责任人', '上线日期'
        ]
        ws2.append(headers)
        # 为表头行（第1行）设置样式
        for col_num, header in enumerate(headers, 1):
            cell = ws2.cell(row=1, column=col_num)
            cell.font = header_font
            cell.fill = header_fill
            cell.border = thin_border
            cell.alignment = center_alignment
        # 写入数据
        for item in query:
            ws2.append([
                item.classify_code,
                item.item_code,
                item.item_name,
                item.desc,
                '',
                '1',
                'COMMON',
                item.language,
                item.sort,
                '', '', '', '', '', '',

                item.principal,
                item.online_date.strftime('%Y-%m-%d')
            ])
        # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
        ws2.freeze_panes = 'A2'
        # 启用自动筛选：从 A1 到最后一列最后一行
        last_col_letter = ws2.cell(row=1, column=ws2.max_column).column_letter
        last_row = ws2.max_row
        ws2.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
        # 调整列宽
        ws2.column_dimensions['A'].width = 37  # Classify Code
        ws2.column_dimensions['B'].width = 40  # Item Code
        ws2.column_dimensions['C'].width = 40  # Item Name
        ws2.column_dimensions['D'].width = 18  # Desc
        ws2.column_dimensions['E'].width = 18  # Parent Item
        ws2.column_dimensions['F'].width = 10  # Status
        ws2.column_dimensions['G'].width = 18  # Lookup Type
        ws2.column_dimensions['H'].width = 11  # Language
        ws2.column_dimensions['I'].width = 10  # Sort
        ws2.column_dimensions['J'].width = 10  # Attribute1
        ws2.column_dimensions['K'].width = 10  # Attribute2
        ws2.column_dimensions['L'].width = 10  # Attribute3
        ws2.column_dimensions['M'].width = 10  # Attribute4
        ws2.column_dimensions['N'].width = 10  # Attribute5
        ws2.column_dimensions['O'].width = 10  # Attribute6
        ws2.column_dimensions['P'].width = 13  # 责任人
        ws2.column_dimensions['Q'].width = 13  # 上线日期
        # 设置某几列单元格居中显示
        for col in ['E', 'F', 'G', 'H', 'I', 'Q']:
            for row in ws2.iter_rows(min_col=ws2[col + '1'].column, max_col=ws2[col + '1'].column):
                for cell in row:
                    cell.alignment = Alignment(horizontal='center', vertical='center')

        # 3、Lookup手工删除
        query = LookupDelete.query
        query = query.filter(LookupDelete.delete_flag == 'N')
        if principal: query = query.filter(LookupDelete.principal.ilike(f'%{principal}%'))
        if online_date: query = query.filter(LookupDelete.online_date == f'{online_date}')
        if created_by: query = query.filter(LookupDelete.created_by.ilike(f'%{created_by}%'))
        query = query.order_by(LookupDelete.online_date.desc(), LookupDelete.last_update_date.desc())
        query = query.paginate(page=1, per_page=1000000, error_out=False)
        lookup_delete_total = query.total
        # 创建第三个工作表
        ws3 = wb.create_sheet("Lookup手工删除")
        # 写入表头
        headers = [
            'Classify Code',
            'Item Code',
            '操作',
            '责任人', '上线日期'
        ]
        ws3.append(headers)
        # 为表头行（第1行）设置样式
        for col_num, header in enumerate(headers, 1):
            cell = ws3.cell(row=1, column=col_num)
            cell.font = header_font
            cell.fill = header_fill
            cell.border = thin_border
            cell.alignment = center_alignment
        # 写入数据
        for item in query:
            ws3.append([
                item.classify_code,
                item.item_code,
                '手工删除',
                item.principal,
                item.online_date.strftime('%Y-%m-%d')
            ])
        # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
        ws3.freeze_panes = 'A2'
        # 启用自动筛选：从 A1 到最后一列最后一行
        last_col_letter = ws3.cell(row=1, column=ws3.max_column).column_letter
        last_row = ws3.max_row
        ws3.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
        # 调整列宽
        ws3.column_dimensions['A'].width = 37  # Classify Code
        ws3.column_dimensions['B'].width = 40  # Item Code
        ws3.column_dimensions['C'].width = 10  # 操作
        ws3.column_dimensions['D'].width = 13  # 责任人
        ws3.column_dimensions['E'].width = 13  # 上线日期
        # 设置某几列单元格居中显示
        for col in ['C', 'E']:
            for row in ws3.iter_rows(min_col=ws3[col + '1'].column, max_col=ws3[col + '1'].column):
                for cell in row:
                    cell.alignment = Alignment(horizontal='center', vertical='center')

        # 4、Lookup手工失效
        query = LookupDisable.query
        query = query.filter(LookupDisable.delete_flag == 'N')
        if principal: query = query.filter(LookupDisable.principal.ilike(f'%{principal}%'))
        if online_date: query = query.filter(LookupDisable.online_date == f'{online_date}')
        if created_by: query = query.filter(LookupDisable.created_by.ilike(f'%{created_by}%'))
        query = query.order_by(LookupDisable.online_date.desc(), LookupDisable.last_update_date.desc())
        query = query.paginate(page=1, per_page=1000000, error_out=False)
        lookup_disable_total = query.total
        # 创建第四个工作表
        ws4 = wb.create_sheet("Lookup手工失效")
        # 写入表头
        headers = [
            'Classify Code',
            'Item Code',
            '操作',
            '责任人', '上线日期'
        ]
        ws4.append(headers)
        # 为表头行（第1行）设置样式
        for col_num, header in enumerate(headers, 1):
            cell = ws4.cell(row=1, column=col_num)
            cell.font = header_font
            cell.fill = header_fill
            cell.border = thin_border
            cell.alignment = center_alignment
        # 写入数据
        for item in query:
            ws4.append([
                item.classify_code,
                item.item_code,
                '手工失效',
                item.principal,
                item.online_date.strftime('%Y-%m-%d')
            ])
        # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
        ws4.freeze_panes = 'A2'
        # 启用自动筛选：从 A1 到最后一列最后一行
        last_col_letter = ws4.cell(row=1, column=ws4.max_column).column_letter
        last_row = ws4.max_row
        ws4.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
        # 调整列宽
        ws4.column_dimensions['A'].width = 37  # Classify Code
        ws4.column_dimensions['B'].width = 40  # Item Code
        ws4.column_dimensions['C'].width = 10  # 操作
        ws4.column_dimensions['D'].width = 13  # 责任人
        ws4.column_dimensions['E'].width = 13  # 上线日期
        # 设置某几列单元格居中显示
        for col in ['C', 'E']:
            for row in ws4.iter_rows(min_col=ws4[col + '1'].column, max_col=ws4[col + '1'].column):
                for cell in row:
                    cell.alignment = Alignment(horizontal='center', vertical='center')

        if lookup_total > 0 or lookup_item_total > 0 or lookup_delete_total > 0 or lookup_disable_total > 0:
            # 将 workbook 保存到 BytesIO
            excel_buffer = BytesIO()
            wb.save(excel_buffer)
            excel_buffer.seek(0)
            filename = "jalor-saasLookup-" + version_string + date_string + ".xlsx"
            # 添加到 ZIP
            zip_file.writestr(filename, excel_buffer.read())

        # 菜单 生成excel文件
        # 1、导出 catalog 的数据
        query = Catalog.query
        query = query.filter(Catalog.delete_flag == 'N')
        if principal: query = query.filter(Catalog.principal.ilike(f'%{principal}%'))
        if online_date: query = query.filter(Catalog.online_date == f'{online_date}')
        if created_by: query = query.filter(Catalog.created_by.ilike(f'%{created_by}%'))
        query = query.order_by(Catalog.online_date.desc(), Catalog.last_update_date.desc())
        query = query.paginate(page=1, per_page=1000000, error_out=False)
        catalog_total = query.total
        wb = Workbook()
        ws = wb.active
        ws.title = "SiteNode"
        # 写入表头
        headers = [
            'Parent Node Name',
            'Name',
            'Description',
            'URL',
            'Order',
            'Visible',
            'Open Link Type',
            'Control',
            'AccessName',
            'Location',
            '责任人', '上线日期'
        ]
        ws.append(headers)
        # 为表头行（第1行）设置样式
        for col_num, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col_num)
            cell.font = header_font
            cell.fill = header_fill
            cell.border = thin_border
            cell.alignment = center_alignment
        # 写入数据
        for item in query:
            ws.append([
                # 数据 - 业务字段
                item.parent_node_name,
                item.name,
                item.description,
                item.url,
                item.order,
                '2',
                item.open_link_type,
                '1',
                'saasoneservice$',
                '',
                item.principal,
                item.online_date.strftime('%Y-%m-%d')
            ])
        # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
        ws.freeze_panes = 'A2'
        # 启用自动筛选：从 A1 到最后一列最后一行
        last_col_letter = ws.cell(row=1, column=ws.max_column).column_letter
        last_row = ws.max_row
        ws.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
        # 调整列宽
        ws.column_dimensions['A'].width = 47  # Parent Node Name
        ws.column_dimensions['B'].width = 47  # Name
        ws.column_dimensions['C'].width = 23  # Description
        ws.column_dimensions['D'].width = 47  # URL
        ws.column_dimensions['E'].width = 11  # Order
        ws.column_dimensions['F'].width = 11  # Visible
        ws.column_dimensions['G'].width = 19  # Open Link Type
        ws.column_dimensions['H'].width = 11  # Control
        ws.column_dimensions['I'].width = 17  # AccessName
        ws.column_dimensions['J'].width = 10  # Location
        ws.column_dimensions['K'].width = 13  # 责任人
        ws.column_dimensions['L'].width = 13  # 上线日期
        # 设置某几列单元格居中显示
        for col in ['E', 'F', 'H', 'I', 'L']:
            for row in ws.iter_rows(min_col=ws[col + '1'].column, max_col=ws[col + '1'].column):
                for cell in row:
                    cell.alignment = Alignment(horizontal='center', vertical='center')

        # 2、导出 菜单修改 的数据
        query = CatalogModify.query
        query = query.filter(CatalogModify.delete_flag == 'N')
        if principal: query = query.filter(CatalogModify.principal.ilike(f'%{principal}%'))
        if online_date: query = query.filter(CatalogModify.online_date == f'{online_date}')
        if created_by: query = query.filter(CatalogModify.created_by.ilike(f'%{created_by}%'))
        query = query.order_by(CatalogModify.online_date.desc(), CatalogModify.last_update_date.desc())
        query = query.paginate(page=1, per_page=1000000, error_out=False)
        catalog_modify_total = query.total
        # 创建第二个工作表
        ws2 = wb.create_sheet("菜单修改")
        # 写入表头
        headers = [
            '事情',
            '菜单在界面的位置',
            '操作',
            '在公服平台修改',
            '责任人', '上线日期'
        ]
        ws2.append(headers)
        # 为表头行（第1行）设置样式
        for col_num, header in enumerate(headers, 1):
            cell = ws2.cell(row=1, column=col_num)
            cell.font = header_font
            cell.fill = header_fill
            cell.border = thin_border
            cell.alignment = center_alignment
        # 写入数据
        for item in query:
            ws2.append([
                item.event,
                item.saas_location,
                item.operation,
                item.jalor_location,
                item.principal,
                item.online_date.strftime('%Y-%m-%d')
            ])
        # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
        ws2.freeze_panes = 'A2'
        # 启用自动筛选：从 A1 到最后一列最后一行
        last_col_letter = ws2.cell(row=1, column=ws2.max_column).column_letter
        last_row = ws2.max_row
        ws2.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
        # 调整列宽
        ws2.column_dimensions['A'].width = 60  # 事情
        ws2.column_dimensions['B'].width = 60  # 菜单在界面的位置
        ws2.column_dimensions['C'].width = 10  # 操作
        ws2.column_dimensions['D'].width = 60  # 在公服平台修改
        ws2.column_dimensions['E'].width = 13  # 责任人
        ws2.column_dimensions['F'].width = 13  # 上线日期
        # 设置某几列单元格居中显示
        for col in ['C', 'F']:
            for row in ws2.iter_rows(min_col=ws2[col + '1'].column, max_col=ws2[col + '1'].column):
                for cell in row:
                    cell.alignment = Alignment(horizontal='center', vertical='center')

        # 3、菜单删除
        query = CatalogDelete.query
        query = query.filter(CatalogDelete.delete_flag == 'N')
        if principal: query = query.filter(CatalogDelete.principal.ilike(f'%{principal}%'))
        if online_date: query = query.filter(CatalogDelete.online_date == f'{online_date}')
        if created_by: query = query.filter(CatalogDelete.created_by.ilike(f'%{created_by}%'))
        query = query.order_by(CatalogDelete.online_date.desc(), CatalogDelete.last_update_date.desc())
        query = query.paginate(page=1, per_page=1000000, error_out=False)
        catalog_delete_total = query.total
        # 创建第三个工作表
        ws3 = wb.create_sheet("菜单删除")
        # 写入表头
        headers = [
            '事情',
            '菜单在界面的位置',
            '操作',
            '在公服平台修改',
            '责任人', '上线日期'
        ]
        ws3.append(headers)
        # 为表头行（第1行）设置样式
        for col_num, header in enumerate(headers, 1):
            cell = ws3.cell(row=1, column=col_num)
            cell.font = header_font
            cell.fill = header_fill
            cell.border = thin_border
            cell.alignment = center_alignment
        # 写入数据
        for item in query:
            ws3.append([
                item.event,
                item.saas_location,
                item.operation,
                item.jalor_location,
                item.principal,
                item.online_date.strftime('%Y-%m-%d')
            ])
        # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
        ws3.freeze_panes = 'A2'
        # 启用自动筛选：从 A1 到最后一列最后一行
        last_col_letter = ws3.cell(row=1, column=ws3.max_column).column_letter
        last_row = ws3.max_row
        ws3.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
        # 调整列宽
        ws3.column_dimensions['A'].width = 60  # 事情
        ws3.column_dimensions['B'].width = 60  # 菜单在界面的位置
        ws3.column_dimensions['C'].width = 10  # 操作
        ws3.column_dimensions['D'].width = 60  # 在公服平台修改
        ws3.column_dimensions['E'].width = 13  # 责任人
        ws3.column_dimensions['F'].width = 13  # 上线日期
        # 设置某几列单元格居中显示
        for col in ['C', 'F']:
            for row in ws3.iter_rows(min_col=ws3[col + '1'].column, max_col=ws3[col + '1'].column):
                for cell in row:
                    cell.alignment = Alignment(horizontal='center', vertical='center')
        if catalog_total > 0 or catalog_modify_total > 0 or catalog_delete_total > 0:
            # 将 workbook 保存到 BytesIO
            excel_buffer = BytesIO()
            wb.save(excel_buffer)
            excel_buffer.seek(0)
            filename = "jalor-SiteNode-" + version_string + date_string + ".xlsx"
            # 添加到 ZIP
            zip_file.writestr(filename, excel_buffer.read())

        # 权限集 生成excel文件
        # 1、导出 function 的数据
        query = Function.query
        query = query.filter(Function.delete_flag == 'N')
        if principal: query = query.filter(Function.principal.ilike(f'%{principal}%'))
        if online_date: query = query.filter(Function.online_date == f'{online_date}')
        if created_by: query = query.filter(Function.created_by.ilike(f'%{created_by}%'))
        query = query.order_by(Function.online_date.desc(), Function.last_update_date.desc())
        query = query.paginate(page=1, per_page=1000000, error_out=False)
        function_total = query.total
        wb = Workbook()
        ws = wb.active
        ws.title = "Functions Basic Information"
        # 写入表头
        headers = [
            'Function Name',
            'Function Name En',
            'Description',
            'DescriptionEn',
            'EnableFlag',
            '责任人', '上线日期'
        ]
        ws.append(headers)
        # 为表头行（第1行）设置样式
        for col_num, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col_num)
            cell.font = header_font
            cell.fill = header_fill
            cell.border = thin_border
            cell.alignment = center_alignment
        # 写入数据
        for item in query:
            ws.append([
                item.function_name,
                '',
                '',
                '',
                'Y',
                item.principal,
                item.online_date.strftime('%Y-%m-%d')
            ])
        # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
        ws.freeze_panes = 'A2'
        # 启用自动筛选：从 A1 到最后一列最后一行
        last_col_letter = ws.cell(row=1, column=ws.max_column).column_letter
        last_row = ws.max_row
        ws.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
        # 调整列宽
        ws.column_dimensions['A'].width = 45  # Function Name
        ws.column_dimensions['B'].width = 23  # Function Name En
        ws.column_dimensions['C'].width = 16  # Description
        ws.column_dimensions['D'].width = 18  # DescriptionEn
        ws.column_dimensions['E'].width = 16  # EnableFlag
        ws.column_dimensions['F'].width = 13  # 责任人
        ws.column_dimensions['G'].width = 13  # 上线日期
        # 设置某几列单元格居中显示
        for col in ['E', 'G']:
            for row in ws.iter_rows(min_col=ws[col + '1'].column, max_col=ws[col + '1'].column):
                for cell in row:
                    cell.alignment = Alignment(horizontal='center', vertical='center')

        # 2、导出 权限集权限点 的数据
        query = FunctionPermission.query
        query = query.filter(FunctionPermission.delete_flag == 'N')
        if principal: query = query.filter(FunctionPermission.principal.ilike(f'%{principal}%'))
        if online_date: query = query.filter(FunctionPermission.online_date == f'{online_date}')
        if created_by: query = query.filter(FunctionPermission.created_by.ilike(f'%{created_by}%'))
        query = query.order_by(FunctionPermission.online_date.desc(), FunctionPermission.last_update_date.desc())
        query = query.paginate(page=1, per_page=1000000, error_out=False)
        function_permission_total = query.total
        # 创建第二个工作表
        ws2 = wb.create_sheet("Functions Permission")
        # 写入表头
        headers = [
            'Function Name',
            'Resource Code',
            'Permession Operation Code',
            'Resource Type',
            'Bastion Sharing Name',
            '责任人', '上线日期'
        ]
        ws2.append(headers)
        # 为表头行（第1行）设置样式
        for col_num, header in enumerate(headers, 1):
            cell = ws2.cell(row=1, column=col_num)
            cell.font = header_font
            cell.fill = header_fill
            cell.border = thin_border
            cell.alignment = center_alignment
        # 写入数据
        for item in query:
            ws2.append([
                item.function_name,
                item.r_uid,
                item.pr_uid,
                'Service',
                item.app_name,
                item.principal,
                item.online_date.strftime('%Y-%m-%d')
            ])
        # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
        ws2.freeze_panes = 'A2'
        # 启用自动筛选：从 A1 到最后一列最后一行
        last_col_letter = ws2.cell(row=1, column=ws2.max_column).column_letter
        last_row = ws2.max_row
        ws2.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
        # 调整列宽
        ws2.column_dimensions['A'].width = 45  # Function Name
        ws2.column_dimensions['B'].width = 45  # Resource Code
        ws2.column_dimensions['C'].width = 50  # Permession Operation Code
        ws2.column_dimensions['D'].width = 20  # Resource Type
        ws2.column_dimensions['E'].width = 27  # Bastion Sharing Name
        ws2.column_dimensions['F'].width = 13  # 责任人
        ws2.column_dimensions['G'].width = 13  # 上线日期
        # 设置某几列单元格居中显示
        for col in ['D', 'G']:
            for row in ws2.iter_rows(min_col=ws2[col + '1'].column, max_col=ws2[col + '1'].column):
                for cell in row:
                    cell.alignment = Alignment(horizontal='center', vertical='center')

        # 3、权限集菜单
        query = FunctionCatalog.query
        query = query.filter(FunctionCatalog.delete_flag == 'N')
        if principal: query = query.filter(FunctionCatalog.principal.ilike(f'%{principal}%'))
        if online_date: query = query.filter(FunctionCatalog.online_date == f'{online_date}')
        if created_by: query = query.filter(FunctionCatalog.created_by.ilike(f'%{created_by}%'))
        query = query.order_by(FunctionCatalog.online_date.desc(), FunctionCatalog.last_update_date.desc())
        query = query.paginate(page=1, per_page=1000000, error_out=False)
        function_catalog_total = query.total
        # 创建第三个工作表
        ws3 = wb.create_sheet("Functions Catalog")
        # 写入表头
        headers = [
            'Function Name',
            'Parent Node Name',
            'Name',
            'Ctype',
            '责任人', '上线日期'
        ]
        ws3.append(headers)
        # 为表头行（第1行）设置样式
        for col_num, header in enumerate(headers, 1):
            cell = ws3.cell(row=1, column=col_num)
            cell.font = header_font
            cell.fill = header_fill
            cell.border = thin_border
            cell.alignment = center_alignment
        # 写入数据
        for item in query:
            ws3.append([
                item.function_name,
                item.parent_node_name,
                item.name,
                'saasoneservice',
                item.principal,
                item.online_date.strftime('%Y-%m-%d')
            ])
        # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
        ws3.freeze_panes = 'A2'
        # 启用自动筛选：从 A1 到最后一列最后一行
        last_col_letter = ws3.cell(row=1, column=ws3.max_column).column_letter
        last_row = ws3.max_row
        ws3.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
        # 调整列宽
        ws3.column_dimensions['A'].width = 45  # Function Name
        ws3.column_dimensions['B'].width = 65  # Parent Node Name
        ws3.column_dimensions['C'].width = 65  # Name
        ws3.column_dimensions['D'].width = 20  # Ctype
        ws3.column_dimensions['E'].width = 15  # 责任人
        ws3.column_dimensions['F'].width = 15  # 上线日期
        # 设置某几列单元格居中显示
        for col in ['D', 'F']:
            for row in ws3.iter_rows(min_col=ws3[col + '1'].column, max_col=ws3[col + '1'].column):
                for cell in row:
                    cell.alignment = Alignment(horizontal='center', vertical='center')

        # 4、权限集维度
        query = FunctionDimension.query
        query = query.filter(FunctionDimension.delete_flag == 'N')
        if principal: query = query.filter(FunctionDimension.principal.ilike(f'%{principal}%'))
        if online_date: query = query.filter(FunctionDimension.online_date == f'{online_date}')
        if created_by: query = query.filter(FunctionDimension.created_by.ilike(f'%{created_by}%'))
        query = query.order_by(FunctionDimension.online_date.desc(), FunctionDimension.last_update_date.desc())
        query = query.paginate(page=1, per_page=1000000, error_out=False)
        function_dimension_total = query.total
        # 创建第四个工作表
        ws4 = wb.create_sheet("Functions Dimension")
        # 写入表头
        headers = [
            'Function Name',
            'Dimension Code',
            '责任人', '上线日期'
        ]
        ws4.append(headers)
        # 为表头行（第1行）设置样式
        for col_num, header in enumerate(headers, 1):
            cell = ws4.cell(row=1, column=col_num)
            cell.font = header_font
            cell.fill = header_fill
            cell.border = thin_border
            cell.alignment = center_alignment
        # 写入数据
        for item in query:
            ws4.append([
                item.function_name,
                item.dimension_code,
                item.principal,
                item.online_date.strftime('%Y-%m-%d')
            ])
        # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
        ws4.freeze_panes = 'A2'
        # 启用自动筛选：从 A1 到最后一列最后一行
        last_col_letter = ws4.cell(row=1, column=ws4.max_column).column_letter
        last_row = ws4.max_row
        ws4.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
        # 调整列宽
        ws4.column_dimensions['A'].width = 50  # Function Name
        ws4.column_dimensions['B'].width = 35  # Dimension Code
        ws4.column_dimensions['C'].width = 15  # 责任人
        ws4.column_dimensions['D'].width = 15  # 上线日期
        # 设置某几列单元格居中显示
        for col in ['D']:
            for row in ws4.iter_rows(min_col=ws4[col + '1'].column, max_col=ws4[col + '1'].column):
                for cell in row:
                    cell.alignment = Alignment(horizontal='center', vertical='center')

        # 5、Functions FrontendResource
        # 创建第五个工作表
        ws5 = wb.create_sheet("Functions FrontendResource")
        # 写入表头
        headers = [
            'Function Name',
            'Code',
            'AppName'
        ]
        ws5.append(headers)

        if function_total > 0 or function_permission_total > 0 or \
                function_catalog_total > 0 or function_dimension_total > 0:
            # 将 workbook 保存到 BytesIO
            excel_buffer = BytesIO()
            wb.save(excel_buffer)
            excel_buffer.seek(0)
            filename = "jalor-Function-" + version_string + date_string + ".xlsx"
            # 添加到 ZIP
            zip_file.writestr(filename, excel_buffer.read())

        # 角色 生成excel文件
        # 1、导出 role 的数据
        query = Role.query
        query = query.filter(Role.delete_flag == 'N')
        if principal: query = query.filter(Role.principal.ilike(f'%{principal}%'))
        if online_date: query = query.filter(Role.online_date == f'{online_date}')
        if created_by: query = query.filter(Role.created_by.ilike(f'%{created_by}%'))
        query = query.order_by(Role.online_date.desc(), Role.last_update_date.desc())
        query = query.paginate(page=1, per_page=1000000, error_out=False)
        role_total = query.total
        wb = Workbook()
        ws = wb.active
        ws.title = "CommonRoles Basic Information"
        # 写入表头
        headers = [
            'Role Name',
            'Role Code',
            'Role English Name',
            'Role NameCn',
            'Description',
            'Description En',
            'Status',
            'Application Status',
            'Owner',
            'Role Level',
            'Role Type',
            '责任人', '上线日期'
        ]
        ws.append(headers)
        # 为表头行（第1行）设置样式
        for col_num, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col_num)
            cell.font = header_font
            cell.fill = header_fill
            cell.border = thin_border
            cell.alignment = center_alignment
        # 写入数据
        for item in query:
            ws.append([
                item.role_name,
                item.role_name,
                item.role_name,
                item.role_name,
                item.role_name,
                item.role_name,
                '1',
                '1',
                '',
                'PROJECT',
                '0',
                item.principal,
                item.online_date.strftime('%Y-%m-%d')
            ])
        # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
        ws.freeze_panes = 'A2'
        # 启用自动筛选：从 A1 到最后一列最后一行
        last_col_letter = ws.cell(row=1, column=ws.max_column).column_letter
        last_row = ws.max_row
        ws.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
        # 调整列宽
        ws.column_dimensions['A'].width = 37  # Role Name
        ws.column_dimensions['B'].width = 37  # Role Code
        ws.column_dimensions['C'].width = 37  # Role English Name
        ws.column_dimensions['D'].width = 37  # Role NameCn
        ws.column_dimensions['E'].width = 37  # Description
        ws.column_dimensions['F'].width = 37  # Description En
        ws.column_dimensions['G'].width = 12  # Status
        ws.column_dimensions['H'].width = 22  # Application Status
        ws.column_dimensions['I'].width = 12  # Owner
        ws.column_dimensions['J'].width = 16  # Role Level
        ws.column_dimensions['K'].width = 15  # Role Type
        ws.column_dimensions['L'].width = 15  # 责任人
        ws.column_dimensions['M'].width = 15  # 上线日期
        # 设置某几列单元格居中显示
        for col in ['G', 'H', 'J', 'K', 'M']:
            for row in ws.iter_rows(min_col=ws[col + '1'].column, max_col=ws[col + '1'].column):
                for cell in row:
                    cell.alignment = Alignment(horizontal='center', vertical='center')

        # 2、导出 角色权限集 的数据
        query = RoleFunction.query
        query = query.filter(RoleFunction.delete_flag == 'N')
        if principal: query = query.filter(RoleFunction.principal.ilike(f'%{principal}%'))
        if online_date: query = query.filter(RoleFunction.online_date == f'{online_date}')
        if created_by: query = query.filter(RoleFunction.created_by.ilike(f'%{created_by}%'))
        query = query.order_by(RoleFunction.online_date.desc(), RoleFunction.last_update_date.desc())
        query = query.paginate(page=1, per_page=1000000, error_out=False)
        role_function_total = query.total
        # 创建第二个工作表
        ws2 = wb.create_sheet("Roles Function")
        # 写入表头
        headers = [
            'Role Name',
            'Function Name',
            '责任人', '上线日期'
        ]
        ws2.append(headers)
        # 为表头行（第1行）设置样式
        for col_num, header in enumerate(headers, 1):
            cell = ws2.cell(row=1, column=col_num)
            cell.font = header_font
            cell.fill = header_fill
            cell.border = thin_border
            cell.alignment = center_alignment
        # 写入数据
        for item in query:
            ws2.append([
                item.role_name,
                item.function_name,
                item.principal,
                item.online_date.strftime('%Y-%m-%d')
            ])
        # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
        ws2.freeze_panes = 'A2'
        # 启用自动筛选：从 A1 到最后一列最后一行
        last_col_letter = ws2.cell(row=1, column=ws2.max_column).column_letter
        last_row = ws2.max_row
        ws2.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
        # 调整列宽
        ws2.column_dimensions['A'].width = 37  # Role Name
        ws2.column_dimensions['B'].width = 50  # Function Name
        ws2.column_dimensions['C'].width = 15  # 责任人
        ws2.column_dimensions['D'].width = 15  # 上线日期
        # 设置某几列单元格居中显示
        for col in ['D']:
            for row in ws2.iter_rows(min_col=ws2[col + '1'].column, max_col=ws2[col + '1'].column):
                for cell in row:
                    cell.alignment = Alignment(horizontal='center', vertical='center')

        # 3、取消角色权限集
        query = RoleFunctionDelete.query
        query = query.filter(RoleFunctionDelete.delete_flag == 'N')
        if principal: query = query.filter(RoleFunctionDelete.principal.ilike(f'%{principal}%'))
        if online_date: query = query.filter(RoleFunctionDelete.online_date == f'{online_date}')
        if created_by: query = query.filter(RoleFunctionDelete.created_by.ilike(f'%{created_by}%'))
        query = query.order_by(RoleFunctionDelete.online_date.desc(), RoleFunctionDelete.last_update_date.desc())
        query = query.paginate(page=1, per_page=1000000, error_out=False)
        role_function_delete_total = query.total
        # 创建第三个工作表
        ws3 = wb.create_sheet("取消角色权限集")
        # 写入表头
        headers = [
            'Role Name',
            'Function Name',
            '操作',
            '责任人', '上线日期'
        ]
        ws3.append(headers)
        # 为表头行（第1行）设置样式
        for col_num, header in enumerate(headers, 1):
            cell = ws3.cell(row=1, column=col_num)
            cell.font = header_font
            cell.fill = header_fill
            cell.border = thin_border
            cell.alignment = center_alignment
        # 写入数据
        for item in query:
            ws3.append([
                item.role_name,
                item.function_name,
                item.operation,
                item.principal,
                item.online_date.strftime('%Y-%m-%d')
            ])
        # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
        ws3.freeze_panes = 'A2'
        # 启用自动筛选：从 A1 到最后一列最后一行
        last_col_letter = ws3.cell(row=1, column=ws3.max_column).column_letter
        last_row = ws3.max_row
        ws3.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
        # 调整列宽
        ws3.column_dimensions['A'].width = 37  # Role Name
        ws3.column_dimensions['B'].width = 50  # Function Name
        ws3.column_dimensions['C'].width = 15  # 操作
        ws3.column_dimensions['D'].width = 15  # 责任人
        ws3.column_dimensions['E'].width = 15  # 上线日期
        # 设置某几列单元格居中显示
        for col in ['C', 'E']:
            for row in ws3.iter_rows(min_col=ws3[col + '1'].column, max_col=ws3[col + '1'].column):
                for cell in row:
                    cell.alignment = Alignment(horizontal='center', vertical='center')

        if role_total > 0 or role_function_total > 0 or role_function_delete_total > 0:
            # 将 workbook 保存到 BytesIO
            excel_buffer = BytesIO()
            wb.save(excel_buffer)
            excel_buffer.seek(0)
            filename = "jalor-CommonRole-" + version_string + date_string + ".xlsx"
            # 添加到 ZIP
            zip_file.writestr(filename, excel_buffer.read())

        # 数据字典 生成excel文件
        query = Property.query
        query = query.filter(Property.delete_flag == 'N')
        if principal: query = query.filter(Property.principal.ilike(f'%{principal}%'))
        if online_date: query = query.filter(Property.online_date == f'{online_date}')
        if created_by: query = query.filter(Property.created_by.ilike(f'%{created_by}%'))

        app_name_list = [
            "tmcCashplan",
            "tmcInstruction",
            "plancommon",
            "monitorExport",
            "TreasuryGuard",
            "tmcDataCashplan",
            "monitorExportHis"
        ]
        for app_name in app_name_list:
            query_plan = query.filter(Property.app_name == app_name)
            query_plan = query_plan.order_by(Property.online_date.desc(), Property.last_update_date.desc())
            query_plan = query_plan.paginate(page=1, per_page=1000000, error_out=False)
            property_plan_total = query_plan.total
            if property_plan_total > 0:
                excel_buffer = generate_excel_file(app_name, principal, online_date, created_by)
                filename = "jalor-Registry-" + app_name + "-" + version_string + date_string + ".xlsx"
                # 添加到 ZIP
                zip_file.writestr(filename, excel_buffer.read())

    # 准备响应
    zip_buffer.seek(0)
    # 构造 Response 返回
    return Response(
        zip_buffer.getvalue(),
        mimetype='application/zip',
        headers={
            'Content-Disposition': 'attachment; filename=jalor-' + version_string + date_string + '.zip',
            'Content-Type': 'application/zip'
        }
    )
